# 索引的优点：加快对表中数据的查找或排序
# 索引的缺点：创建需要空间成本，维护需要时间成本，降低表的增删改效率
# 索引的使用场景：数据库中表的数据量较大的情况下，对于查询响应时间不能满足业务需求，可以使用索引提升查询效率。

sql1 = 'create index index_name_name on teachers(name)'
sql2 = 'create index index_name_age on teachers(age)'

# 索引的使用


# 1.查看索引的使用情况
sql_show = 'show status like "%Handler_read%";'

# 2.where后使用or，索引失效(少用)
# 使用索引
sql = 'explain select * from students where cno = "01" or name = "张三"'
# 没有使用索引
sql = 'explain select * from students where age = 18 or name = "张三"'

# 3.使用like:%开头
# 使用索引
sql = 'explain select * from students where  name = "三%"'
# 没有使用索引
sql = 'explain select * from students where  name = "%三"'
sql = 'explain select * from students where  name = "%三%"'

# 4.复合索引遵循最左前缀
sql = 'create index index_name_age on teachers(name,age)'
# 使用索引
sql = 'explain select * from students where  name = "张三" and age =18'
sql = 'explain select * from students where  age =18 and name = "张三"'
sql = 'explain select * from students where  name = "张三" and cno="01"'
# 没有使用索引
sql = 'explain select * from students where  age =18 and cno="01"'

# 5.列类型为字符串：要是有引号，否则不使用索引
# 使用索引
sql = 'explain select * from students where  name = "张三"'
# 没有使用索引
sql = 'explain select * from students where  name = 张三'

# 6. in:索引失效
# 使用索引
sql = 'explain select * from students where  name = "张三"'
# 没有使用索引
sql = 'explain select * from students where  name in  ("张三","李四")'

# 7.date_format():格式化时间，再进行比较
# 使用索引
sql = 'explain select * from students where  create_time>"2020-1-1"'
# 没有使用索引
sql = 'explain select * from students where  date_format(create_time,"%Y-%m-%d") > "2020-1-1" '

# 8.对于order by  group by   union   distinct  中的字段出现在where条件中时，才会利用索引！
